﻿using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using QJY.Common;
using QJY.Data;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace QJY.API
{
    #region 系统基础模块
    public class JWHelp
    {

        #region  基础方法





        /// <summary>
        /// 获取系部权限
        /// </summary>
        /// <param name="strUser"></param>
        /// <param name="strqxlist"></param>
        /// <returns></returns>
        public DataTable GetQXXB(string strUser, string strqxlist)
        {

            DataTable dt = new QJ_JWB().GetDTByCommand("SELECT xbdm,xbmc, (rtrim(xbmc)+'--'+xbdm) as xbxx, jxxb, '0' as qxxb, '0' as kcxb FROM xbdm");

            JArray arrpro = new JArray();
            arrpro.Add(new JObject() { { "@_whfs", "查询用户权限系部代码" } });
            arrpro.Add(new JObject() { { "@_yhdm", strUser } });
            arrpro.Add(new JObject() { { "@_qxdjlist", strqxlist } });
            DataTable dtqx = new QJ_JWB().GetDataTableByPR("pr_yhdm_qxdj_xbdmzydm", arrpro);

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int m = 0; m < dtqx.Rows.Count; m++)
                {
                    if (dtqx.Rows[m]["xbdm"].ToString().Trim() == dt.Rows[i]["xbdm"].ToString().Trim())
                    {
                        dt.Rows[i]["qxxb"] = "1";
                    }
                }
            }

            DataTable dtkc = new QJ_JWB().GetDTByCommand("SELECT DISTINCT KCXBDM FROM KCDM");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int m = 0; m < dtkc.Rows.Count; m++)
                {
                    if (dtkc.Rows[m]["kcxbdm"].ToString().Trim() == dt.Rows[i]["xbdm"].ToString().Trim())
                    {
                        dt.Rows[i]["kcxb"] = "1";
                    }
                }
            }


            return dt;
        }


        /// <summary>
        /// 获取校区信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetXiaoQ()
        {

            JArray arrpro = new JArray();
            arrpro.Add(new JObject() { { "@_whfs", "查询校区信息NEW" } });
            DataTable dtReturn = new QJ_JWB().GetDataTableByPR("pr_xbdm_sele", arrpro);
            return dtReturn;
        }


        /// <summary>
        /// 获取专业数据
        /// </summary>
        /// <returns></returns>
        public DataTable GetZY()
        {
            DataTable dtReturn = new QJ_JWB().GetDTByCommand("SELECT * FROM ( SELECT DISTINCT rtrim(xbdm) as [value], rtrim(ADDR) as label, '0' as pval from zydm UNION SELECT DISTINCT rtrim(zydm) as [value], rtrim(zymc) as label, rtrim(xbdm) as pval from zydm) AS A ORDER BY PVAL,LABEL");
            return dtReturn;
        }


        /// <summary>
        /// 获取系统学期数据
        /// </summary>
        /// <param name="strUser"></param>
        /// <param name="strqxlist"></param>
        /// <returns></returns>
        public DataTable GetXQ()
        {



            JArray arrproxq = new JArray();
            arrproxq.Add(new JObject() { { "@_whfs", "查询当前学期" } });
            DataTable dtXQ = new QJ_JWB().GetDataTableByPR("pr_xbdm_sele", arrproxq);


            JArray arrpro = new JArray();
            arrpro.Add(new JObject() { { "@_whfs", "查询所有学期" } });
            DataTable dtReturn = new QJ_JWB().GetDataTableByPR("pr_xbdm_sele", arrpro);
            dtReturn.Columns.Add("isnow");
            for (int i = 0; i < dtReturn.Rows.Count; i++)
            {
                if (dtReturn.Rows[i]["xq"].ToString() == dtXQ.Rows[0]["xq"].ToString())
                {
                    dtReturn.Rows[i]["isnow"] = "Y";

                }
                else
                {
                    dtReturn.Rows[i]["isnow"] = "N";
                }
            }
            return dtReturn;
        }

        /// <summary>
        /// 获取可用教师信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetJSXX()
        {
            DataTable dtJC = new QJ_JWB().GetDTByCommand("select jsdm, jsmc, RTRIM(xbdm) xbdm, dbo.fn_xbdm_xbmc(xbdm) xbmc,RTRIM(jsmc)+'('+RTRIM(jsdm)+')-'+dbo.fn_xbdm_xbmc(xbdm) sm from jsdm where xbdm<>'' and jsmc !='思想政治理论课教研部'");
            return dtJC;
        }



        /// <summary>
        /// 年级
        /// </summary>
        /// <returns></returns>
        public DataTable GetNJ()
        {
            DataTable dt = new QJ_JWB().GetDTByCommand("select distinct nj from dbo.bj order by nj desc");
            return dt;
        }



        /// <summary>
        /// 课程类别
        /// </summary>
        /// <returns></returns>
        public DataTable Getkclb()
        {
            DataTable dt = new QJ_JWB().GetDTByCommand("select lbdh,lbmc,rtrim(lbmc)+'-'+rtrim(lbdh) lbxx from dbo.kclb order by lbdh");
            return dt;
        }

        /// <summary>
        /// 系部
        /// </summary>
        /// <returns></returns>
        public string GetALLXB()
        {
            DataTable dt = new QJ_JWB().GetDTByCommand("select stuff((select ','+ltrim(xbdm) from xbdm for xml path('')),1,1,'')");
            return dt.Rows[0][0].ToString();
        }



        /// <summary>
        /// 根据日期获取当前周次,默认当前日期所在周次
        /// </summary>
        /// <param name="strDate"></param>
        /// <returns></returns>
        public string GetWeekByDate(string strDate = "")
        {
            DataTable dtkxrq = new QJ_JWB().GetDTByCommand("select kxsj from xtrq");
            string strkxrq = dtkxrq.Rows[0]["kxsj"].ToString();

            DataTable dt = new QJ_JWB().GetDTByCommand("select ceiling((DATEDIFF(day, '" + strkxrq + "', getdate())+1)/7.0) as week from dbo.xtrq");
            return dt.Rows[0][0].ToString();
        }





        /// <summary>
        /// 根据学号获取学生信息
        /// </summary>
        /// <param name="strXH"></param>
        /// <returns></returns>
        public XsInfo GetXSInfo(string qjcode)
        {
            string strXH = EncrpytHelper.DecodeDES(qjcode, "qijiekeji");
            DataTable dt = new QJ_JWB().GetDTByCommand("select * from dbo.xs_v where xh='" + strXH + "'");
            XsInfo xs = new XsInfo();
            if (dt.Rows.Count > 0)
            {
                xs.xh = dt.Rows[0]["xh"].ToString();
                xs.bh = dt.Rows[0]["bh"].ToString();
                xs.bj = dt.Rows[0]["bj"].ToString();
                xs.bmmc = dt.Rows[0]["bmmc"].ToString();
                xs.mz = dt.Rows[0]["mzm"].ToString();
                xs.nj = dt.Rows[0]["nj"].ToString();
                xs.sfzh = dt.Rows[0]["SFZH"].ToString();
                xs.xb = dt.Rows[0]["xb"].ToString();
                xs.xbdm = dt.Rows[0]["xbdm"].ToString();
                xs.xbmc = dt.Rows[0]["xbmc"].ToString();
                xs.xh = dt.Rows[0]["XH"].ToString();
                xs.xjqk = dt.Rows[0]["xjqk"].ToString();
                xs.xm = dt.Rows[0]["xm"].ToString();
                xs.zydm = dt.Rows[0]["zydm"].ToString();
                xs.sylb = dt.Rows[0]["Expr1"].ToString();



            }
            return xs;
        }

        /// <summary>
        /// 获取操作权限信息
        /// </summary>
        /// <param name="qxdm"></param>
        /// <returns></returns>
        public DataTable GetCZQX(string qxdm)
        {
            DataTable dt = new JH_Auth_UserB().GetDTByCommand("select * from dbo.qj_czqx where czstime<GETDATE() and czetime>GETDATE() and qxdm='" + qxdm + "'");
            return dt;
        }

        #region 班级
        public string GetBJRS(string strBH)
        {
            string strRS = "";
            DataTable dt = new QJ_JWB().GetDTByCommand(" SELECT bjrs RS  FROM BJ WHERE BH IN('" + strBH.Trim(',').ToFormatLike() + "')");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                strRS = strRS + dt.Rows[i]["RS"].ToString() + ",";
            }
            return strRS.TrimEnd(',');
        }


        /// <summary>
        /// 非毕业班
        /// </summary>
        /// <returns></returns>
        public DataTable GetBJTree()
        {
            DataTable dt = new QJ_JWB().GetDTByCommand("SELECT DISTINCT NJ AS id, NJ AS label  FROM bj WHERE zxqk='在校学习' ORDER BY nj DESC");
            DataTable dtBJ = new QJ_JWB().GetDTByCommand("SELECT bh as id ,BJ as label,nj  FROM bj WHERE zxqk='在校学习' ORDER BY nj DESC");

            dt.Columns.Add("children", Type.GetType("System.Object"));
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string nj = dt.Rows[i]["id"].ToString();
                dt.Rows[i]["children"] = dtBJ.Where("nj='" + nj + "'");
            }
            return dt;
        }


        #endregion
        #endregion



        #region 辅助方法

        /// <summary>
        /// 获取学号-湖南益阳
        /// </summary>
        /// <returns></returns>
        public string GetXH(string strXBXH, string strZYXH, string strCCXH)
        {
            string strxh = strXBXH + strZYXH + strCCXH;
            string strSQL = "SELECT  ISNULL(max(kcdm),0) as MAXFORMMUM FROM kcdm WHERE kcdm LIKE '"+ strxh + "%'";
            DataTable dt = new QJ_JWB().GetDTByCommand(strSQL);

            string strNow = dt.Rows[0][0].ToString();
            if (strNow == "0")
            {
                strxh = strxh + "0001";
            }
            else
            {
                int maxxh = int.Parse(strNow.Substring(strxh.Length + 1));
                strxh = strxh + (maxxh + 1).ToString("0000");
            }
            return strxh;
        }

        #endregion
    }

    public class QJ_JWB : BaseEFDaoJW<QJ_JW>
    {

        /// <summary>
        /// 执行存储过程并获取返回值
        /// </summary>
        /// <param name="strProName"></param>
        /// <param name="prlist"></param>
        /// <returns></returns>
        public DataTable GetDataTableByPR(string strProName, JArray prlist)
        {
            List<SugarParameter> ListP = new List<SugarParameter>();
            foreach (var item in prlist)
            {

                Dictionary<string, string> values = JsonConvert.DeserializeObject<Dictionary<string, string>>(item.ToString());

                string pname = values.Keys.FirstOrDefault();
                string pvalue = values.Values.FirstOrDefault();
                ListP.Add(new SugarParameter(pname, pvalue));
            }
            DataTable dt = new QJ_JWB().Db.Ado.UseStoredProcedure().GetDataTable(strProName, ListP);
            return dt;
        }

    }

    /// <summary>
    /// 学生信息
    /// </summary>
    public class XsInfo
    {
        /// <summary>
        /// 学号
        /// </summary>
        public string xh { get; set; }

        /// <summary>
        /// 班号
        /// </summary>
        public string bh { get; set; }

        /// <summary>
        /// 姓名
        /// </summary>
        public string xm { get; set; }

        /// <summary>
        /// 性别
        /// </summary>
        public string xb { get; set; }
        /// <summary>
        /// 名族
        /// </summary>
        public string mz { get; set; }
        /// <summary>
        /// 校区
        /// </summary>
        public string bmmc { get; set; }
        /// <summary>
        /// 系部代码
        /// </summary>
        public string xbdm { get; set; }
        /// <summary>
        /// 系部名称
        /// </summary>
        public string xbmc { get; set; }
        /// <summary>
        /// 年级
        /// </summary>
        public string nj { get; set; }
        /// <summary>
        /// 学籍情况
        /// </summary>
        public string xjqk { get; set; }
        /// <summary>
        /// 班级
        /// </summary>
        public string bj { get; set; }
        /// <summary>
        /// 专业代码
        /// </summary>
        public string zydm { get; set; }
        /// <summary>
        /// 身份证号码
        /// </summary>
        public string sfzh { get; set; }
        /// <summary>
        /// 生源类别
        /// </summary>
        public string sylb { get; set; }



    }






















    #endregion



}
